package cn.ytian.scashier;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import static cn.ytian.scashier.Config.*;
import static cn.ytian.scashier.YLib.pln;

public class YMysql {
    public static HashMap<String, Product> productMap = new HashMap<String, Product>();
    public static List<SalesItem> salesList = new ArrayList<>();
    static String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static String JDBC_URL = String.format("jdbc:mysql://%s:%s/%s", JDBC_url, JDBC_post, JDBC_database);
    static Connection conn = null;
    static Statement stmt = null;

    public static void LinkMysql() {
        try {
            Class.forName(JDBC_DRIVER);
            System.out.println("Mysql链接中...");
            conn = DriverManager.getConnection(JDBC_URL, JDBC_user, JDBC_password);
            if (conn.getAutoCommit()) {
                pln("Good Mysql链接成功");
            }
            stmt = conn.createStatement();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    static HashMap<String, Product> readProductList() {
        String Sql = "select * from product";
        ResultSet rs = RsSql(Sql);
        HashMap<String, Product> tmpHap = new HashMap<>();
        try {
            while (rs.next()) {
                // 通过字段检索
                String Pid = rs.getString("Pid");
                String Pname = rs.getString("Pname");
                double PPrice = rs.getDouble("PPrice");
                Product tmpP = new Product(Pid, Pname, PPrice);
                tmpHap.put(Pid, tmpP);
            }
            rs.close();
            return tmpHap;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    static List<SalesItem> getSalesList(String Oid) {
        String Sql = "SELECT\n" +
                "\t*\n" +
                "FROM\n" +
                "\torderprod\n" +
                "\tINNER JOIN\n" +
                "\tproduct\n" +
                "\tON \n" +
                "\t\torderprod.P_id = product.Pid\n" +
                "WHERE\n" +
                "\torderprod.O_id = " + Oid;
        ResultSet rs =  RsSql(Sql);
        List<SalesItem> Sales = new ArrayList<>();
        try {
            while (rs.next()) {
                String goodsID = rs.getString("Pid");
                String goodsName = rs.getString("Pname");
                double goodsPrice = rs.getDouble("PPrice");
                int amount = rs.getInt("Num");
                double goodsSubtotal = rs.getDouble("Subtotal");
                SalesItem SaTmp = new SalesItem(goodsID, goodsName, goodsPrice, amount, goodsSubtotal);
                Sales.add(SaTmp);
            }
            rs.close();
            return Sales;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    static List<Order> getOrderList() {
        String Sql = "select * from orderlist";
        ResultSet rs2 = RsSql(Sql);
        List<Order> orders = new ArrayList<>();
        try {
            while (rs2.next()) {
                // 通过字段检索
                String Oid = rs2.getString("Oid");
                //String ODate = stampToDate(rs.getString("ODate"));
                String ODate = rs2.getString("ODate");
                double OAmount = rs2.getDouble("OAmount");
                String OAdmin = rs2.getString("OAdmin");
                String OUser = rs2.getString("OUser");

                List<SalesItem> sales = new ArrayList<>();
                Order OTemp = new Order(Oid, ODate, OAmount, OAdmin, OUser, sales);
                orders.add(OTemp);
            }
            rs2.close();
            return orders;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    static void InsertData(String sql, Object... args) {
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    static void InProduct(Product p) {
        String sql = "INSERT INTO product VALUES (?,?,?) ON DUPLICATE KEY UPDATE Pname=VALUES(Pname),PPrice=VALUES(PPrice);";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, p.getGoodsID());
            ps.setString(2, p.getGoodsName());
            ps.setDouble(3, p.getGoodsPrice());
            ps.execute();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    static void InSales(String Oid, List<SalesItem> sales) {
        String sql = "INSERT INTO orderprod VALUES (?,?,?,?);";
        try {
            for (SalesItem sa : sales) {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1, sa.getGoodsID());
                ps.setString(2, Oid);
                ps.setInt(3, sa.getAmount());
                ps.setDouble(4, sa.getGoodsSubtotal());
                ps.execute();
                ps.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    static void InOrder(Order order) {
        String sql = "INSERT INTO orderlist VALUES (?,null ,?,?,?);";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, order.getOid());
            ps.setDouble(2, order.getOPrice());
            ps.setString(3, order.getAdmin());
            ps.setString(4, order.getClient());
            ps.execute();
            InSales(order.getOid(), order.getSales());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    static void InLProduct(List<Product> pl) {
        for (Product x : pl) {
            InProduct(x);
        }
    }


    static ResultSet RsSql(String sql) {
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery(sql);
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    static boolean RunSql(String sql) {
        String[] sqlList = sql.split(";");
        boolean b = true;
        try {
            int i = 0;
            for (String x : sqlList) {
                i++;
                // pln(i+" " +x);
                b = stmt.executeUpdate(x) > 0 && b;
            }

            return b;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    static void init() {
        String fileName = "src/Data/Data.sql";
        try {
            String sql = Files.readString(Paths.get(fileName));
            RunSql(sql);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    static void load() {
        if (DataType.equals(Data.DTMysql)) {
            LinkMysql();
            init();
        }
    }


}
